﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
//using XLETL.Model;

namespace XLETL.Common
{
    /// <summary>
    /// Class ExcelHelper contains utility methods for manipulating Excel documents
    /// </summary>
    public class ExcelHelper
    {
        public DataTable LoadSpecifiedSheet(string fileName, string sheetName)
        {
            OleDbConnection conn = this.GetXLConnection(fileName);
            DataTable SheetData = null;
            try
            {
                conn.Open();
                //retrieve datareader with data for that sheet			
                OleDbDataAdapter SheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "$]", conn);
                SheetData = new DataTable();
                SheetAdapter.Fill(SheetData);
            }
            catch (Exception)
            {
                throw;
                //Trace.WriteLine(Ex.StackTrace);
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return SheetData;
        }

        /*
        public Dictionary<string, ArrayList> LoadSpecifiedSheet(TaskInfo taskInfo)
        {
            OleDbConnection conn = this.GetXLConnection(taskInfo.TemplatePath);

            Dictionary<string, ArrayList> sheetData = new Dictionary<string, ArrayList>();

            try
            {
                conn.Open();
                //retrieve datareader with data for that sheet

                using (OleDbCommand com = conn.CreateCommand())
                {
                    com.CommandType = CommandType.Text;
                    com.CommandText = "select * from [" + taskInfo.TaskDetails.SourceSheetName + "]";

                    using (OleDbDataReader reader = com.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            foreach (SourceColumnInfo sci in taskInfo.SourceColumns)
                            {
                                DataTable dt = new DataTable();
                                
                                reader.GetValue(reader.GetOrdinal(sci.Name))
                        }
                    }
                }              
                
            }
            catch (Exception)
            {
                throw;
                //Trace.WriteLine(Ex.StackTrace);
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return sheetData;
        }*/

        private OleDbConnection GetXLConnection(string excelFilePath)
        {
            OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection();

            try
            {
                System.IO.FileInfo oFileInfo = new System.IO.FileInfo(excelFilePath);

                if (oFileInfo.Extension == ".xlsx" || oFileInfo.Extension == ".xlsm")
                {
                    oleDbConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + @";Extended Properties=""Excel 12.0;HDR=YES;""";                    
                }
                else
                {
                    oleDbConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + @";Extended Properties=""Excel 8.0;HDR=YES;""";
                }
            }
            catch (Exception)
            {
                throw;
            }

            return oleDbConnection;
        }

    }
}
